﻿Imports System.Data.OleDb
Imports System.Data.SqlServerCe
Imports System.Xml
Imports System.Text

Public Class PersonalService

    Private Function scriptTablaPersonal(ByVal tipoChar As String, ByVal tipoVarchar As String, ByVal ape_pat As String, ByVal ape_mat As String, ByVal fnac As String) As String
        Return "CREATE TABLE Personal(" & _
                "P_CodPer " & tipoChar & "(8) NOT NULL, " & _
                "P_Nombre " & tipoChar & "(40)," & _
                ape_pat & " " & tipoChar & "(20)," & _
                ape_mat & " " & tipoChar & "(20)," & _
                "P_Cargo " & tipoVarchar & "(2)," & _
                "P_Odei " & tipoChar & "(2)," & _
                "P_Dpto " & tipoChar & "(2)," & _
                "P_Usuario " & tipoChar & "(20)," & _
                "P_Clave " & tipoVarchar & "(8)," & _
                fnac & " " & tipoChar & "(10)," & _
                "CONSTRAINT pk_Personal PRIMARY KEY (P_CodPer)" & _
                ");"
        'P_Fecha = Fecha de Nacimiento
    End Function
    'Para el MDB
    Public Sub crearTablaPersonal(ByVal cmd As OleDbCommand)
        cmd.CommandText = scriptTablaPersonal("char", "varchar", "P_ApePater", "P_ApeMater", "P_Fecha")
        cmd.ExecuteNonQuery()
    End Sub
    'Para el SDF
    Public Sub crearTablaPersonal(ByVal cmd As SqlCeCommand)
        cmd.CommandText = scriptTablaPersonal("nchar", "nvarchar", "P_ApePaterno", "P_ApeMaterno", "P_FechaNac")
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub crearTablaCargo(ByVal cmd As SqlCeCommand)
        cmd.CommandText = "CREATE TABLE Cargo(" & _
                "CodCargo nchar(2) NOT NULL, " & _
                "DesCargo nchar(35)," & _
                "CONSTRAINT pk_Cargo PRIMARY KEY (CodCargo)" & _
                ");"
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub crearTablaOdei(ByVal cmd As SqlCeCommand)
        cmd.CommandText = "CREATE TABLE Odei(" & _
                "CodOdei nchar(2) NOT NULL, " & _
                "NomOdei nchar(25)," & _
                "CONSTRAINT pk_Cargo PRIMARY KEY (CodOdei)" & _
                ");"
        cmd.ExecuteNonQuery()
    End Sub

    Public Sub iterarPersonal(ByRef doc As XmlDocument, ByVal comando As PersonalCommand)
        Dim listaNodos As XmlNodeList
        Dim nodo As XmlNode

        'Obtenemos la lista de los nodos "name"
        listaNodos = doc.SelectNodes("/xml/Personal/Registro")

        'Iniciamos el ciclo de lectura
        For Each nodo In listaNodos
            Dim codCargo As String = nodo.ChildNodes.Item(4).InnerText

            Dim list As New List(Of String)
            list.Add("01")
            list.Add("02")
            list.Add("03")
            list.Add("04")
            list.Add("05")
            list.Add("08")
            list.Add("09")

            '01 Supervisor(a) Nacional.
            '02 Coordinador(a).
            '03 Supervisor(a) Local.
            '04 Encuestador(a).
            '05 Soporte Técnico.
            '08 Informáticos. 
            '09 Metodólogo(a).

            Dim continuar As Boolean = False
            For Each cod In list
                If cod = codCargo Then
                    continuar = True
                End If
            Next

            If continuar Then
                Dim p As New Personal
                p.dni = nodo.ChildNodes.Item(0).InnerText
                p.nombre = nodo.ChildNodes.Item(1).InnerText
                p.ap_pat = nodo.ChildNodes.Item(2).InnerText
                p.ap_mat = nodo.ChildNodes.Item(3).InnerText
                p.cargo = findCargoByCodigo(doc, codCargo)
                'Conviertiendo cargo de char(2) a char(1). Porque el SDF debe tener cargos de longitud 1
                p.cargo.codigo = p.cargo.codigo.Substring(1, 1)
                p.odei = nodo.ChildNodes.Item(5).InnerText
                p.dpto = nodo.ChildNodes.Item(6).InnerText
                p.usuario = nodo.ChildNodes.Item(7).InnerText
                p.clave = nodo.ChildNodes.Item(8).InnerText.Trim
                p.fecha_nac = nodo.ChildNodes.Item(9).InnerText

                comando.procesar(p)
            End If

        Next
    End Sub

    Public Function findCargoByCodigo(ByRef doc As XmlDocument, ByVal codigo As String) As Cargo
        Dim listaNodos As XmlNodeList
        Dim nodo As XmlNode

        listaNodos = doc.SelectNodes("/xml/Cargo/Registro")

        For Each nodo In listaNodos
            Dim cod As String = nodo.ChildNodes.Item(0).InnerText

            If cod.Equals(codigo) Then
                Dim c As New Cargo
                c.codigo = cod
                c.nombre = nodo.ChildNodes.Item(1).InnerText
                Return c
            End If

        Next

        Return Nothing
    End Function


    Public Sub insertarTablaCargo(ByRef doc As XmlDocument, ByVal cmd As System.Data.Common.DbCommand)
        Dim listaNodos As XmlNodeList
        Dim nodo As XmlNode

        listaNodos = doc.SelectNodes("/xml/Cargo/Registro")

        For Each nodo In listaNodos
            Dim c As New Cargo
            c.codigo = nodo.ChildNodes.Item(0).InnerText
            c.nombre = nodo.ChildNodes.Item(1).InnerText

            cmd.CommandText = "insert into Cargo values (" & _
            "'" & c.codigo & "'," & _
            "'" & c.nombre & "')"

            cmd.ExecuteNonQuery()
        Next
    End Sub

    Public Sub insertarTablaOdei(ByRef doc As XmlDocument, ByVal cmd As System.Data.Common.DbCommand)
        Dim listaNodos As XmlNodeList
        Dim nodo As XmlNode

        listaNodos = doc.SelectNodes("/xml/Odei/Registro")

        For Each nodo In listaNodos
            Dim od As New Odei
            od.codigo = nodo.ChildNodes.Item(0).InnerText
            od.nombre = nodo.ChildNodes.Item(1).InnerText

            cmd.CommandText = "insert into Odei values (" & _
            "'" & od.codigo & "'," & _
            "'" & od.nombre & "')"

            cmd.ExecuteNonQuery()
        Next
    End Sub
End Class
